BEGIN TRANSACTION
GO
ALTER TABLE dbo.Invoice_BankEForm ADD
	GDNo varchar(50) NULL,
	GDDate datetime NULL,
	GDAmount decimal(15, 4) NOT NULL CONSTRAINT DF_Invoice_BankEForm_GDAmount DEFAULT 0
GO
COMMIT

----------------------------------------------------------------------------------------------

BEGIN TRANSACTION
GO
UPDATE    Invoice_BankEForm
SET              GDNo =
                          (SELECT     GD
                            FROM          Invoice_Bank
                            WHERE      (Invoice_BankEForm.inv_BankID = Inv_BankID))
GO
COMMIT

----------------------------------------------------------------------------------------------

BEGIN TRANSACTION
GO
UPDATE    Invoice_BankEForm
SET              GDDate =
                          (SELECT     BLDate
                            FROM          Invoice_Bank
                            WHERE      (Invoice_BankEForm.inv_BankID = Inv_BankID))
GO
COMMIT

----------------------------------------------------------------------------------------------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[vw_Ledger]
AS
SELECT     '01' + CONVERT(Varchar, dbo.Voucher.VoucherID) AS VoucherID, dbo.Voucher.VoucherDate, dbo.Voucher.VoucherNo, dbo.VoucherDetails.AccountNo, 
                      dbo.Accounts.AccountTitle, dbo.Accounts.ParentAccount, CASE WHEN dbo.VoucherDetails.DrOrCr = 'Dr' THEN dbo.VoucherDetails.Amount ELSE 0 END AS Debit, 
                      CASE WHEN dbo.VoucherDetails.DrOrCr = 'Cr' THEN dbo.VoucherDetails.Amount ELSE 0 END AS Credit, dbo.VoucherDetails.DrOrCr, 
                      CASE WHEN dbo.VoucherDetails.AccountNo = dbo.VoucherDetails.SubPartyID THEN dbo.VoucherDetails.Remarks ELSE dbo.VoucherDetails.Remarks + ' ' + ISNULL(LEFT(Accounts_1.AccountTitle,
                       15), '') END AS Remarks, dbo.VoucherDetails.Qty, dbo.Voucher.NoOfContainers, dbo.Accounts.Currency, 
                      CASE WHEN dbo.VoucherDetails.AccountNo = dbo.VoucherDetails.SubPartyID THEN dbo.VoucherDetails.Remarks ELSE dbo.VoucherDetails.Remarks + ' ' + ISNULL(LEFT(Accounts_1.AccountTitle,
                       15), '') END AS mRemarks, '' AS ContainerNo, dbo.VoucherDetails.SubPartyID, Accounts_1.AccountTitle AS SubPartyName
FROM         dbo.Voucher INNER JOIN
                      dbo.VoucherDetails ON dbo.Voucher.VoucherID = dbo.VoucherDetails.VoucherID LEFT OUTER JOIN
                      dbo.Accounts ON dbo.VoucherDetails.AccountNo = dbo.Accounts.AccountNo LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_1 ON dbo.VoucherDetails.SubPartyID = Accounts_1.AccountNo
UNION ALL
SELECT     '02' + CONVERT(Varchar, dbo.Invoice_Agent.Inv_AgentID) AS VoucherID, dbo.Invoice_AgentDetail.SaleDate, dbo.Invoice_Agent.Inv_AgentVNo, 
                      dbo.Invoice_AgentDetail.AgentID, Accounts_7.AccountTitle, Accounts_7.ParentAccount, 0 AS Debit, dbo.Invoice_AgentDetail.NetAmount AS Credit, 'Cr' AS DrOrCr, 
                      dbo.Invoice_AgentDetail.Remarks, dbo.Invoice_AgentDetail.QTY, dbo.Invoice_Agent.NoOfContainers, Accounts_7.Currency, 
                      dbo.Invoice_Agent.Remarks + ' ' + dbo.Invoice_Agent.BENo AS mRemarks, ISNULL(dbo.Invoice_Agent.ContainerNo, '') AS ContainerNo, NULL AS SubPartyID, NULL 
                      AS SubPartyName
FROM         dbo.Invoice_Agent LEFT OUTER JOIN
                      dbo.Invoice_AgentDetail LEFT OUTER JOIN
                      dbo.Groups ON dbo.Invoice_AgentDetail.DTypeID = dbo.Groups.GroupID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_7 ON dbo.Invoice_AgentDetail.AgentID = Accounts_7.AccountNo ON 
                      dbo.Invoice_Agent.Inv_AgentID = dbo.Invoice_AgentDetail.inv_AgentID
UNION ALL
SELECT     '03' + CONVERT(Varchar, dbo.Invoice_Air.Inv_AirID) AS VoucherID, dbo.Invoice_Air.AWBDate, dbo.Invoice_Air.Inv_AirVNo, dbo.Invoice_Air.ConsigneeID, 
                      Accounts_6.AccountTitle, Accounts_6.ParentAccount, dbo.Invoice_Air.NetInvoice AS Debit, 0 AS Credit, 'Dr' AS DrOrCr, 
                      dbo.Invoice_AirItem.DRemarks + ' ' + dbo.Varietys.VarietyName AS Remarks, dbo.Invoice_Air.SumQTY, 0 AS NoofContainers, Accounts_6.Currency, 
                      dbo.Invoice_Air.Remarks AS mRemarks, '' AS ContainerNo, NULL AS SubPartyID, NULL AS SubPartyName
FROM         dbo.Varietys LEFT OUTER JOIN
                      dbo.Invoice_AirItem ON dbo.Varietys.VarietyID = dbo.Invoice_AirItem.VarietyID RIGHT OUTER JOIN
                      dbo.Accounts AS Accounts_6 RIGHT OUTER JOIN
                      dbo.Invoice_Air ON Accounts_6.AccountNo = dbo.Invoice_Air.ConsigneeID ON dbo.Invoice_AirItem.inv_AirID = dbo.Invoice_Air.Inv_AirID
UNION ALL
SELECT     '04' + CONVERT(Varchar, dbo.Invoice_CTN.Inv_CTNID) AS VoucherID, dbo.Invoice_CTN.Inv_CTNDate, dbo.Invoice_CTN.Inv_CTNVNo, dbo.Invoice_CTNitem.PartyID, 
                      Accounts_5.AccountTitle, Accounts_5.ParentAccount, 0 AS Debit, dbo.Invoice_CTNitem.CrAmount, 'Cr' AS DrorCr, 
                      dbo.Invoice_CTNitem.DRemarks + ' ' + Varietys_4.VarietyName AS Remarks, dbo.Invoice_CTNitem.Bot AS QTY, dbo.Invoice_CTNitem.Sheet AS NoofContainers, 
                      Accounts_5.Currency, dbo.Invoice_CTN.Remarks AS mRemarks, '' AS ContainerNo, NULL AS SubPartyID, NULL AS SubPartyName
FROM         dbo.Varietys AS Varietys_4 RIGHT OUTER JOIN
                      dbo.Invoice_CTNitem ON Varietys_4.VarietyID = dbo.Invoice_CTNitem.VarietyID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_5 ON dbo.Invoice_CTNitem.PartyID = Accounts_5.AccountNo RIGHT OUTER JOIN
                      dbo.Invoice_CTN ON dbo.Invoice_CTNitem.Inv_CTNID = dbo.Invoice_CTN.Inv_CTNID
UNION ALL
SELECT     '05' + CONVERT(Varchar, dbo.Invoice_PUR.Inv_PURID) AS VoucherID, dbo.Invoice_PUR.Inv_PURDate, dbo.Invoice_PUR.Inv_PURVNo, dbo.Invoice_PURitem.PartyID, 
                      Accounts_4.AccountTitle, Accounts_4.ParentAccount, 0 AS Debit, dbo.Invoice_PURitem.Amount, 'Cr' AS DrOrCr, 
                      dbo.Invoice_PURitem.DRemarks + ' ' + Varietys_3.VarietyName AS Remarks, dbo.Invoice_PURitem.Qty, 0 AS Credit, Accounts_4.Currency, 
                      dbo.Invoice_PUR.Remarks AS mRemarks, '' AS ContainerNo, NULL AS SubPartyID, NULL AS SubPartyName
FROM         dbo.Invoice_PURitem LEFT OUTER JOIN
                      dbo.Varietys AS Varietys_3 ON dbo.Invoice_PURitem.VarietyID = Varietys_3.VarietyID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_4 ON dbo.Invoice_PURitem.PartyID = Accounts_4.AccountNo RIGHT OUTER JOIN
                      dbo.Invoice_PUR ON dbo.Invoice_PURitem.Inv_PURID = dbo.Invoice_PUR.Inv_PURID
UNION ALL
SELECT     '06' + CONVERT(Varchar, Invoice_PUR_1.Inv_PURID) AS VoucherID, Invoice_PUR_1.Inv_PURDate, Invoice_PUR_1.Inv_PURVNo, Invoice_PURitem_1.DlrID, 
                      Accounts_4.AccountTitle, Accounts_4.ParentAccount, 0 AS Debit, Invoice_PURitem_1.Amount, 'Cr' AS DrOrCr, 
                      Invoice_PURitem_1.DRemarks + ' ' + Varietys_2.VarietyName AS Remarks, Invoice_PURitem_1.Qty, 0 AS Credit, Accounts_4.Currency, 
                      Invoice_PUR_1.Remarks AS mRemakrs, '' AS ContainerNo, NULL AS SubPartyID, NULL AS SubPartyName
FROM         dbo.Invoice_PURitem AS Invoice_PURitem_1 LEFT OUTER JOIN
                      dbo.Varietys AS Varietys_2 ON Invoice_PURitem_1.VarietyID = Varietys_2.VarietyID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_4 ON Invoice_PURitem_1.DlrID = Accounts_4.AccountNo RIGHT OUTER JOIN
                      dbo.Invoice_PUR AS Invoice_PUR_1 ON Invoice_PURitem_1.Inv_PURID = Invoice_PUR_1.Inv_PURID
UNION ALL
SELECT     '07' + CONVERT(Varchar, dbo.Invoice_SSP.Inv_SSPID) AS VoucherID, dbo.Invoice_SSParty.SaleDate, dbo.Invoice_SSP.Inv_SSPVNo, dbo.Invoice_SSParty.DebitID, 
                      Accounts_2.AccountTitle, Accounts_2.ParentAccount, CASE WHEN dbo.Invoice_SSParty.NetSale >= 0 THEN dbo.Invoice_SSParty.NetSale ELSE 0 END AS Debit, 
                      CASE WHEN dbo.Invoice_SSParty.NetSale < 0 THEN ABS(dbo.Invoice_SSParty.NetSale) ELSE 0 END AS Credit, 
                      CASE WHEN dbo.Invoice_SSParty.NetSale >= 0 THEN 'Dr' ELSE 'Cr' END AS DrOrCr, 
                      CASE WHEN dbo.Invoice_SSParty.DebitID = dbo.Invoice_SSParty.SubID THEN dbo.Invoice_SSParty.Remarks + ' ' + ISNULL(dbo.Invoice_SSParty.ContainerNo, '') 
                      + ' ' + ISNULL(Varietys_1.VarietyName, '') ELSE dbo.Invoice_SSParty.Remarks + ' ' + ISNULL(dbo.Invoice_SSParty.ContainerNo, '') 
                      + ' ' + ISNULL(Varietys_1.VarietyName, '') + ' ' + LEFT(Accounts_3.AccountTitle, 15) END AS Remarks, dbo.Invoice_SSParty.TQTY, dbo.Invoice_SSP.NoOfContainers, 
                      Accounts_2.Currency, dbo.Invoice_SSP.Remarks + ' ' + ISNULL(dbo.Invoice_SSP.ContainerNo, '') + ' ' + dbo.Items.ItemName AS mRemarks, 
                      ISNULL(dbo.Invoice_SSP.ContainerNo, '') AS ContainerNo, dbo.Invoice_SSParty.SubID, Accounts_3.AccountTitle AS SubPartyName
FROM         dbo.Invoice_SSParty LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_3 ON dbo.Invoice_SSParty.SubID = Accounts_3.AccountNo LEFT OUTER JOIN
                      dbo.Items ON dbo.Invoice_SSParty.ItemID = dbo.Items.ItemID LEFT OUTER JOIN
                      dbo.Varietys AS Varietys_1 ON dbo.Invoice_SSParty.VarietyID = Varietys_1.VarietyID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_2 ON dbo.Invoice_SSParty.DebitID = Accounts_2.AccountNo RIGHT OUTER JOIN
                      dbo.Invoice_SSP ON dbo.Invoice_SSParty.inv_SSPID = dbo.Invoice_SSP.Inv_SSPID
UNION ALL
SELECT     '08' + CONVERT(Varchar, dbo.Invoice_Trans.Inv_TransID) AS VoucherID, dbo.Invoice_TransDetail.SaleDate, dbo.Invoice_Trans.Inv_TransVNo, 
                      dbo.Invoice_TransDetail.TransID, Accounts_1.AccountTitle, Accounts_1.ParentAccount, 0 AS Debit, dbo.Invoice_TransDetail.NetFreight, 'Cr' AS DrOrCr, 
                      dbo.Invoice_TransDetail.Remarks, 0 AS QTY, dbo.Invoice_Trans.NoOfContainers, Accounts_1.Currency, dbo.Invoice_Trans.Remarks AS mRemarks, 
                      '' AS Container, NULL AS SubPartyID, NULL AS SubPartyName
FROM         dbo.Invoice_TransDetail RIGHT OUTER JOIN
                      dbo.Invoice_Trans ON dbo.Invoice_TransDetail.inv_TransID = dbo.Invoice_Trans.Inv_TransID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_1 ON dbo.Invoice_TransDetail.TransID = Accounts_1.AccountNo